* Census_countyDB_proc.do
* 2014.05.07
* Last update 2014.11.01: loop was missing many tabs in exel workbooks. Revising code.
* Processes Census counties DB, which collects county info from many agencies

clear
clear matrix
clear mata
capture log close
set more off
set matsize 800
set maxvar 30000
timer clear 1
timer on 1
set scheme s2mono

local work "/DIRECTORY"

log using "`work'/logs/Census_countyDB_proc.log", replace

* Saving source files in Stata format
local filelist: dir "`work'/data/counties db/Spreadsheets" files "*.xls"
display `filelist'
foreach z in `filelist' {
	display "`z'"
	* Code to set number of tabs based on filename
	import excel using "`work'/data/counties db/Spreadsheets/`z'", describe
	local tabs = r(N_worksheet)
	forval y = 1/`tabs' {
		import excel using "`work'/data/counties db/Spreadsheets/`z'", sheet("Sheet`y'") firstrow clear
		rename STCOU fips
		save "`work'/data/counties db/Formatted/`z'_sheet`y'.dta", replace
	}
}

* Merging variables into wide data set
clear
gen fips=""
local filelist: dir "`work'/data/counties db/Formatted" files "*.dta"
foreach x in `filelist' {
	merge 1:1 fips using "`work'/data/counties db/Formatted/`x'", nogenerate
}



* Saving
save "`work'/data/counties db/CountiesDB.dta", replace









timer off 1
timer list 1
capture log close




